﻿

-- B1: TÍNH HẾT TẤT CẢ CÁC DÒNG TIỀN GHI CÓ VÀO TÀI KHOẢN CỦA KHÁCH HÀNG ( CẢ CURRENT ACCOUNT VÀ TERM DEPOSIT)
		-- FT VS TT MAP VÀO ACCTNO VÀO DPTB_MASTER (SEGMENT = SME)

   --- XỬ LÝ BẢNG FT_HIST
 DECLARE @MIN_DATE DATETIME = (  SELECT MAX(BUSINESS_DATE) FROM  S16_FT_HIST)
 DECLARE @MAX_DATE DATETIME = (  SELECT MAX(BUSINESS_DATE) FROM  SERVER16.VPB_WHR2.DBO.FT_HIST)

 DECLARE @STRMINDATE NVARCHAR(8) = CONVERT(NVARCHAR(8), @MIN_DATE, 112)
 DECLARE @STRMAXDATE NVARCHAR(8) = CONVERT(NVARCHAR(8), @MAX_DATE, 112)
 --PRINT @STRMINDATE 
 DECLARE @DATECOUNT DATETIME = @MIN_DATE + 1
 PRINT @DATECOUNT

 WHILE @DATECOUNT <= @MAX_DATE
 BEGIN
 PRINT CONVERT(NVARCHAR(10),@DATECOUNT,121)
 DECLARE @STRDATECOUNT NVARCHAR(10) =  CONVERT(NVARCHAR(10), @DATECOUNT, 121)

 INSERT S16_FT_HIST
 SELECT * FROM SERVER16.VPB_WHR2.DBO.FT_HIST
 WHERE BUSINESS_DATE = @STRDATECOUNT

 SET @DATECOUNT = @DATECOUNT + 1
 END

 SELECT * INTO S74_DPTB_MASTER  FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME'
select * from S16_FT_HIST_2016

SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG
  INTO FT_KSSV_201511	
	FROM S16_FT_HIST_2016
		WHERE (
				(DEBIT_ACCOUNT IN (	SELECT ACCTNO  FROM S74_DPTB_MASTER
								WHERE BI_SEGMENT = 'SME' )) 
							OR 
				(CREDIT_ACCOUNT IN (	SELECT ACCTNO  FROM S74_DPTB_MASTER
								WHERE BI_SEGMENT = 'SME' ))
			   )

--SELECT DISTINCT CREDIT_CCY FROM FT_KSSV_201511 WHERE  CIF_DEBIT IS NULL AND CIF_CREDIT IS NULL
 -- UPDATE CIF_DEBIT VÀ CIF_CREDIT

 ALTER TABLE FT_KSSV_201511
 ADD CIF_DEBIT VARCHAR(20),CIF_CREDIT VARCHAR(20),CHECK_CIF	VARCHAR(4), AMOUNT FLOAT, AMOUNT_QD FLOAT, TYGIA FLOAT, CIF VARCHAR(30)

 -- UPDATE CIF_DEBIT
UPDATE FT_KSSV_201511
SET CIF_DEBIT=B.CIF
FROM FT_KSSV_201511 A, ( SELECT *  
							FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME') B
WHERE A.DEBIT_ACCOUNT= B.ACCTNO


 -- UPDATE CIF_DEBIT
UPDATE FT_KSSV_201511
SET CIF_CREDIT=B.CIF
FROM FT_KSSV_201511 A, ( SELECT *  
							FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME') B
WHERE A.CREDIT_ACCOUNT= B.ACCTNO

  -- UPDATE CHECK_CIF	 ( SELECT * FROM FT_KSSV_201511 WHERE CIF IS NULL )

UPDATE FT_KSSV_201511
SET CHECK_CIF = CASE WHEN CIF_DEBIT = CIF_CREDIT THEN '0' ELSE '1' END

-- UPDATE CIF
   UPDATE FT_KSSV_201511
   SET CIF = CIF_CREDIT

   UPDATE FT_KSSV_201511
   SET CIF = CIF_DEBIT
   WHERE CIF IS NULL

 -- UPDATE AMOUNT

 --select max from FT_HIST_2016_FILTER_ACCOUNT


 UPDATE FT_KSSV_201511
 SET AMOUNT = CASE	WHEN CREDIT_AMOUNT NOT LIKE '%Ý%' THEN CREDIT_AMOUNT ELSE DEBIT_AMOUNT END

 -- UPDATE TỶ GIÁ

 UPDATE FT_KSSV_201511
 SET TYGIA = B.MID_RATE
 FROM FT_KSSV_201511 A, (SELECT * FROM SERVER16.VPB_WHR2.DBO.FOCURR_SAVE WHERE  SAVE_DATE = '20151130') B
 WHERE A.CREDIT_CCY =B.CODE 

  --- UPDATE AMOUNT_QD

  UPDATE FT_KSSV_201511
  SET AMOUNT_QD = AMOUNT*TYGIA



SELECT BUSINESS_DATE,ID,TRANSACTION_CODE AS TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG FROM AD_HOC.DBO.TT_HIST_201511 -----CẢ THÁNG, SERVER 16 


 
   --- XỬ LÝ BẢNG TT_HIST


SELECT BUSINESS_DATE,ID,TRANSACTION_CODE AS TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG 
  INTO TT_KSSV_201511	
	FROM AD_HOC.DBO.TT_HIST_201511
		WHERE (
				(DEBIT_ACCOUNT IN (	SELECT ACCTNO  FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME' )) 
							OR 
				(CREDIT_ACCOUNT IN (	SELECT ACCTNO  FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME' ))
			   )

--SELECT DISTINCT CREDIT_CCY FROM FT_KSSV_201511 WHERE  CIF_DEBIT IS NULL AND CIF_CREDIT IS NULL
 -- UPDATE CIF_DEBIT VÀ CIF_CREDIT

 ALTER TABLE TT_KSSV_201511
 ADD CIF_DEBIT VARCHAR(20),CIF_CREDIT VARCHAR(20),CHECK_CIF	VARCHAR(4), AMOUNT FLOAT, AMOUNT_QD FLOAT, TYGIA FLOAT,CIF VARCHAR(30)


 -- UPDATE CIF_DEBIT
UPDATE TT_KSSV_201511
SET CIF_DEBIT=B.CIF
FROM TT_KSSV_201511 A, ( SELECT *  
							FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME') B
WHERE A.DEBIT_ACCOUNT= B.ACCTNO


 -- UPDATE CIF_DEBIT
UPDATE TT_KSSV_201511
SET CIF_CREDIT=B.CIF
FROM TT_KSSV_201511 A, ( SELECT *  
							FROM SERVER74.BICDATA.DBO.DPTB_MASTER
								WHERE BI_SEGMENT = 'SME') B
WHERE A.CREDIT_ACCOUNT= B.ACCTNO

  -- UPDATE CHECK_CIF

UPDATE TT_KSSV_201511
SET CHECK_CIF = CASE WHEN CIF_DEBIT = CIF_CREDIT THEN '0' ELSE '1' END

 -- UPDATE CIF
   UPDATE TT_KSSV_201511
   SET CIF = CIF_CREDIT

   UPDATE TT_KSSV_201511
   SET CIF = CIF_DEBIT
   WHERE CIF IS NULL


 -- UPDATE AMOUNT
	   
	   -- SELECT * FROM TT_KSSV_201511 WHERE DEBIT_AMOUNT LIKE N'%Ý%'

 UPDATE TT_KSSV_201511
 SET AMOUNT = B.AMOUNT
 FROM (SELECT CASE	WHEN ISNUMERIC(CREDIT_AMOUNT) = 1 THEN CREDIT_AMOUNT 
					ELSE DEBIT_AMOUNT END AS AMOUNT, ID
		FROM TT_KSSV_201511) B
WHERE  TT_KSSV_201511.ID = B.ID


 

 -- UPDATE TỶ GIÁ

 UPDATE TT_KSSV_201511
 SET TYGIA = B.MID_RATE
 FROM TT_KSSV_201511 A, (SELECT * FROM SERVER16.VPB_WHR2.DBO.FOCURR_SAVE WHERE  SAVE_DATE = '20151130') B
 WHERE A.CREDIT_CCY =B.CODE 

  --- UPDATE AMOUNT_QD

  UPDATE TT_KSSV_201511
  SET AMOUNT_QD = AMOUNT*TYGIA

---- GHÉP 2 BẢNG TT_KSSV_201511 VÀ FT_KSSV_201511 ( LOẠI NHỮNG TRƯỜNG HỢP CÙNG CHUNG 1 CIF KH)
						 --DROP TABLE FT_TT_KSSV_201511	   -- SELECT * FROM  FT_TT_KSSV_201511_1
SELECT * 
INTO FT_TT_KSSV_201511_1
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM TT_KSSV_201511
 WHERE CHECK_CIF = '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_KSSV_201511
 WHERE CHECK_CIF = '1'
) X

------ TRƯỜNG HỢP CHUNG CIF		 SELECT * FROM 	 FT_TT_KSSV_201511_0
SELECT * 
INTO FT_TT_KSSV_201511_0
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM TT_KSSV_201511
 WHERE CHECK_CIF <> '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_KSSV_201511
 WHERE CHECK_CIF <> '1'
) X


------------------ TRẢ NỢ GỐC-LẠI KHÁCH HÀNG	DROP TABLE DONG_TIEN_GHI_CO_201511	 -- SELECT * FROM FT_TT_KSSV_201511 WHERE 
 
SELECT A.CIF,B.CUS_NAME,B.COMPANY_BOOK,C.BRANCH_NAME_SME,C.ZONE_ID_SME,SUM(A.AMOUNT_QD)/1E9 AS AMOUNT 
INTO DONG_TIEN_GHI_CO_201511 
FROM FT_TT_KSSV_201511_1	A
LEFT JOIN SERVER16.VPB_WHR2.DBO.VPB_CUSTOMER B
ON A.CIF = B.RECID COLLATE DATABASE_DEFAULT
LEFT JOIN CEO_PERFORMANCE.DBO.BRANCH_CODE C
ON B.COMPANY_BOOK=C.BRANCH_ID
WHERE B.COMPANY_BOOK NOT IN (SELECT * FROM CEO_PERFORMANCE.DBO.BRANCHOFF)
AND A.CIF NOT IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM CEO_PERFORMANCE.DBO.SME_QUICKWIN_20160204)
GROUP BY A.CIF,B.CUS_NAME,B.COMPANY_BOOK,C.BRANCH_NAME_SME,C.ZONE_ID_SME 

  ALTER TABLE DONG_TIEN_GHI_CO_201511
  ADD TRA_NO FLOAT

										
		 

UPDATE DONG_TIEN_GHI_CO_201511
SET TRA_NO = B.REPAY_AMOUNT
FROM DONG_TIEN_GHI_CO_201511 A,  (SELECT  CIF, SUM(REPAY_AMOUNT)/1E9 AS REPAY_AMOUNT  
							FROM SERVER74.STAGING.DBO.LNTB_LDPD_REPAYED
							WHERE  REPAY_DATE BETWEEN '20151101' AND '20151130'					------- THAY NGÀY TRẢ NỢ
							AND CIF IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM FT_TT_KSSV_201511_1)
							AND CIF NOT IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM CEO_PERFORMANCE.DBO.SME_QUICKWIN_20160204)
							--AND CO_CODE NOT IN (SELECT * FROM CEO_PERFORMANCE.DBO.BRANCHOFF)
							AND SEGMENT = 'SMES'
							GROUP BY CIF  ) B
WHERE A.CIF=B.CIF COLLATE DATABASE_DEFAULT

  ALTER TABLE DONG_TIEN_GHI_CO_201511
  ADD DOANH_THU_GHI_CO	FLOAT

  UPDATE DONG_TIEN_GHI_CO_201511
  SET DOANH_THU_GHI_CO = ISNULL(AMOUNT,0) - ISNULL(TRA_NO,0) 
 

 SELECT * FROM 	DONG_TIEN_GHI_CO_201511
 WHERE DOANH_THU_GHI_CO <0


